﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OleDb;

namespace John
{
    /// <summary>
    /// MySqlData 的摘要说明
    /// </summary>
    public class MySqlData
    {
        OleDbConnection DataConnection;
        public MySqlData()
        {
            //string ConStr = ConfigurationManager.AppSettings["connectionstring"].ToString();
            string ConStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='|DataDirectory|\xysc.mdb';Persist Security Info=True";
            //string password = ConfigurationManager.AppSettings["datapass"].ToString();
            SymmetricMethod Crypy = new SymmetricMethod();
            //password = Crypy.Decrypto(password);
            // ConStr +="pwd="+ password;
            // ConStr += "pwd=";
            DataConnection = new OleDbConnection(ConStr);
            //string MainConStr=Configuration.
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        private void OpenDataConnection()
        {
            if (DataConnection.State == ConnectionState.Open)
                return;
            try
            {
                DataConnection.Open();
            }
            catch
            {
                throw new Exception("无法打开数据库连接！");

            }
        }


        private void CloseDataConnection()
        {
            if (DataConnection.State == ConnectionState.Closed)
                return;
            try
            {
                DataConnection.Close();
            }
            catch
            {


            }
        }

        //process commad.excutescale
        public string ExcuteScale(string commandStr)
        {
            OleDbCommand Command = new OleDbCommand(commandStr, DataConnection);
            string Value = "";
            OpenDataConnection();
            try
            {
                Value = Command.ExecuteScalar().ToString();
            }
            catch (Exception exp)
            {
                CloseDataConnection();
                throw new Exception("数据读取失败!原因：" + exp.ToString());

            }
            CloseDataConnection();
            return Value;
        }

        //process command.excutennoquery
        public int ExcuteNoQuery(string commandStr)
        {
            OleDbCommand Command = new OleDbCommand(commandStr, DataConnection);
            int rows = 0;
            OpenDataConnection();
            try
            {
                rows = Command.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                CloseDataConnection();
                throw new Exception("数据更新失败!     原因：" + exp.ToString());

            }
            CloseDataConnection();
            return rows;
        }
        /// <summary>
        /// 执行插入操作并返回自动生成主键值
        /// </summary>
        /// <param name="commandStr"></param>
        /// <returns></returns>
        public string ExcuteNoQueryAndSelectKey(string commandStr)
        {
            OleDbCommand Command = new OleDbCommand(commandStr, DataConnection);
            int rows = 0;
            string ID = "";
            OpenDataConnection();
            try
            {
                rows = Command.ExecuteNonQuery();
                if (rows == 1)//插入成功
                {
                    Command.CommandText = "SELECT @@IDENTITY";
                    ID = Command.ExecuteScalar().ToString();
                }
            }
            catch (Exception exp)
            {
                CloseDataConnection();
                throw new Exception("数据更新失败!     原因：" + exp.ToString());

            }
            CloseDataConnection();
            return ID;
        }

        //SELECT a table from db
        public DataTable SelectTable(string commandStr)
        {
            OleDbCommand Command = new OleDbCommand(commandStr, DataConnection);
            OleDbDataAdapter Ad = new OleDbDataAdapter(Command);
            DataTable Dt = new DataTable();
            OpenDataConnection();
            try
            {
                Ad.Fill(Dt);
            }
            catch (Exception exp)
            {
                CloseDataConnection();
                throw new Exception("数据读取失败!  原因：" + exp.ToString());

            }
            CloseDataConnection();
            return Dt;
        }
        /// <summary>
        /// 依据oledbcommand 查询表
        /// </summary>
        /// <param name="com"></param>
        /// <returns></returns>
        public DataTable SelectTable(OleDbCommand com)
        {
            com.Connection = DataConnection;
            OleDbDataAdapter Ad = new OleDbDataAdapter(com);
            DataTable Dt = new DataTable();
            OpenDataConnection();
            try
            {
                Ad.Fill(Dt);
            }
            catch (Exception exp)
            {
                CloseDataConnection();
                throw new Exception("数据读取失败!  原因：" + exp.ToString());

            }
            CloseDataConnection();
            return Dt;
        }

        //Dispose the connection
        public void Dispose()
        {
            DataConnection.Dispose();
        }

        //get server time
        public DateTime GetTime()
        {
            string CommandStr = "SELECT TOP 1 getdate() FROM users ";
            OleDbCommand Command = new OleDbCommand(CommandStr, DataConnection);
            DateTime NowTime = DateTime.Now;
            OpenDataConnection();
            try
            {
                NowTime = Convert.ToDateTime(Command.ExecuteScalar());
            }
            catch
            {
                CloseDataConnection();
                throw new Exception("无法获取服务器的时间!");

            }
            CloseDataConnection();
            return NowTime;
        }





    }

}